﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Ylh.DBUtility
{
    public static class SqlHelper
    {

        private static SqlConnection conn = null;
        private static SqlCommand cmd = null;
        private static SqlDataReader sdr = null;
        private static DataTable dt = null;


        /// <summary>
        ///  打开Connection对象
        /// </summary>
        private static SqlConnection GetConn(string connStr)
        {
            SqlConnection conn = new SqlConnection(connStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

        /// <summary>
        /// 执行不带参数的SQL查询语句或存储过程
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="ct"></param>
        /// <returns>table</returns>
        public static DataTable ExcuteQuery(string SqlConnection, string cmdText, CommandType ct)
        {
            dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn(SqlConnection));
            cmd.CommandType = ct;
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }


        /// <summary>
        /// 重载，返回执行结果信息
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="ct"></param>
        /// <returns></returns>
        public static string ExcuteQueryReturnMessage(string SqlConnection,string cmdText, CommandType ct)
        {
            try
            {
                ExecuteNonQuery(SqlConnection,cmdText, ct);
                return "执行成功！";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }


        /// <summary> 
        /// 执行带参数的SQL查找语句或存储过程
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="param"></param>
        /// <param name="ct"></param>
        /// <returns></returns>
        public static DataTable ExcuteQuery(string SqlConnection, string cmdText, SqlParameter[] param, CommandType ct)
        {
            dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn(SqlConnection));
            cmd.CommandType = ct;
            cmd.Parameters.AddRange(param);
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }

        /// <summary>
        /// 分页通用方法
        /// </summary>
        /// <param name="SqlConnection">连接字符串</param>
        /// <param name="tbname">表名称</param>
        /// <param name="PrimaryKey">主键，逗号隔开可以多个</param>
        /// <param name="PageCurrent">当前页，不是0开始</param>
        /// <param name="PageSize">每页多少条记录</param>
        /// <param name="FieldShow">显示的字段，空为全部</param>
        /// <param name="FieldOrder">排序字段，逗号隔开</param>
        /// <param name="Where">不带‘where’的筛选</param>
        /// <param name="PageCount">1就会返回页数和全部记录，其它只返回记录</param>
        /// <returns></returns>
        public static DataSet GetPageList(string SqlConnection, string tbname, string PrimaryKey, int PageCurrent, int PageSize, string FieldShow, string FieldOrder, string Where, string PageCount)
        {
            DataSet ds = new DataSet();
            string sql = "sp_PageView";
            cmd = new SqlCommand(sql, GetConn(SqlConnection));
            cmd.CommandType = CommandType.StoredProcedure;
            object pagecount_temp = new object();
            pagecount_temp = PageCount == "0" ? (object)DBNull.Value : (object)PageCount;
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@tbname",tbname),
                new SqlParameter("@FieldKey",PrimaryKey),
                new SqlParameter("@PageCurrent",PageCurrent),
                new SqlParameter("@PageSize",PageSize),
                new SqlParameter("@FieldShow",FieldShow),
                new SqlParameter("@FieldOrder",FieldOrder),
                new SqlParameter("@Where",Where),
                new SqlParameter("@PageCount",pagecount_temp)
            };
            cmd.Parameters.AddRange(param);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //try
            //{
                da.Fill(ds);

            //}
            //catch (Exception)
            //{
            //    return null;
            //}
            return ds;
        }


        /// <summary>
        ///  执行不带参数的增删改SQL语句或存储过程
        /// </summary>
        /// <param name="cmdText">增删改SQL语句或存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns>语句影响行数</returns>
        public static int ExecuteNonQuery(string SqlConnection, string cmdText, CommandType ct)
        {
            int res;
            using (cmd = new SqlCommand(cmdText, GetConn(SqlConnection)))
            {
                cmd.CommandType = ct;
                res = cmd.ExecuteNonQuery();
            }
            return res;
        }

        /// <summary>
        ///  执行带参数的增删改SQL语句或存储过程
        /// </summary>
        /// <param name="cmdText">增删改SQL语句或存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns>语句影响行数</returns>
        public static int ExecuteNonQuery(string SqlConnection, string cmdText, SqlParameter[] paras, CommandType ct)
        {
            int res;
            using (cmd = new SqlCommand(cmdText, GetConn(SqlConnection)))
            {
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);
                res = cmd.ExecuteNonQuery();
            }
            return res;
        }

        /// <summary>
        /// 执行带参数的查询SQL语句或存储过程
        /// </summary>
        /// <param name="cmdText">查询SQL语句或存储过程</param>
        /// <param name="paras">参数集合</param>
        /// <param name="ct">命令类型</param>
        /// <returns>标量值</returns>
        public static int ExecuteScalar(string SqlConnection, string cmdText, SqlParameter[] paras, CommandType ct)
        {
            int res;
            using (cmd = new SqlCommand(cmdText, GetConn(SqlConnection)))
            {
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);
                res = (int)cmd.ExecuteScalar();
            }
            return res;
        }




    }
}
